/**
 * 
 */
package wpmp.security.mgr.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Component;

import wpmp.security.mgr.SqlStatics;
import wpmp.security.mgr.base.BaseDao;
import wpmp.security.mgr.entity.Resource;

/**
 * @author Wayne.Wang<5waynewang@gmail.com>
 * @since 10:09:17 AM Mar 18, 2013
 */
@Component
public class ResourceDao extends BaseDao<Resource, Long> {

	private static final String SQL_INSERT_RESOURCE_OPERATION_RELA = "INSERT INTO sys_resource_operation_rela(resource_id,operation_id) VALUES (?,?)";

	private static final String SQL_DELETE_RESOURCE_OPERATION_RELA = "DELETE FROM sys_resource_operation_rela WHERE resource_id=?";

	private static final String SQL_QUERY_ROLE_RESOURCE_OPERATION_RELA = "SELECT b.rela_id,b.operation_id FROM sys_role_resource_rela a,sys_role_resource_operation_rela b WHERE a.id=b.rela_id AND a.resource_id=?";

	private static final String SQL_DELETE_ROLE_RESOURCE_OPERATION_RELA = "DELETE FROM sys_role_resource_operation_rela WHERE rela_id=? AND operation_id=?";

	private static final String SQL_DELETE_ROLE_RESOURCE_OPERATION_RELA2 = "DELETE FROM sys_role_resource_operation_rela WHERE rela_id IN (SELECT id FROM sys_role_resource_rela WHERE resource_id=?)";

	private static final String SQL_INCREASE_ORDER_NUM_WHEN_GE = "UPDATE sys_resource SET order_num=order_num+1 WHERE parent_id=? AND order_num>=?";

	private static final String SQL_INCREASE_ORDER_NUM_WHEN_BETWEEN = "UPDATE sys_resource SET order_num=order_num+1 WHERE parent_id=? AND order_num BETWEEN ? AND ?";

	private static final String SQL_DECREASE_ORDER_NUM_WHEN_BETWEEN = "UPDATE sys_resource SET order_num=order_num-1 WHERE parent_id=? AND order_num BETWEEN ? AND ?";

	private static final String SQL_DECREASE_ORDER_NUM_WHEN_GT = "UPDATE sys_resource SET order_num=order_num-1 WHERE parent_id=? AND order_num > ?";

	private static final String SQL_DELETE_ROLE_RESOURCE_RELA = "DELETE FROM sys_role_resource_rela WHERE resource_id=?";

	private static final String SQL_UPDATE_CHILD_LEVEL = "UPDATE sys_resource SET level=CONCAT(?,SUBSTR(level FROM ?)) WHERE level LIKE CONCAT(?,'%') AND level<>?";

	/**
	 * 更新子类的level
	 * 
	 * <pre>
	 * 同时更新所有直系子类的level
	 * </pre>
	 * 
	 * @param fromLevel
	 * @param toLevel
	 * @return
	 */
	public Integer updateChildLevel(final String fromLevel, final String toLevel) {
		return getJdbcTemplate().execute(SQL_UPDATE_CHILD_LEVEL, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setString(1, toLevel);
				ps.setInt(2, fromLevel.length() + 1);
				ps.setString(3, fromLevel);
				ps.setString(4, fromLevel);
				return ps.executeUpdate();
			}
		});
	}

	/**
	 * 删除角色、资源之前的关系
	 * 
	 * @param resourceId
	 * @return
	 */
	public Integer deleteRoleResourceRela(final Long resourceId) {
		return getJdbcTemplate().execute(SQL_DELETE_ROLE_RESOURCE_RELA, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setLong(1, resourceId);
				return ps.executeUpdate();
			}
		});
	}

	/**
	 * 删除角色、资源之前的关系
	 * 
	 * @param resourceIds
	 * @return
	 */
	public int[] deleteRoleResourceRela(final Long[] resourceIds) {
		return getJdbcTemplate().batchUpdate(SQL_DELETE_ROLE_RESOURCE_RELA, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setLong(1, resourceIds[i]);
			}

			@Override
			public int getBatchSize() {
				return resourceIds.length;
			}
		});
	}

	/**
	 * 当orderNum大于或等于某值的时候递增
	 * 
	 * @param parentId
	 * @param orderNum
	 * @return
	 */
	public Integer increaseOrderNumWhenGe(final Long parentId, final Integer orderNum) {
		return getJdbcTemplate().execute(SQL_INCREASE_ORDER_NUM_WHEN_GE, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setLong(1, parentId);
				ps.setInt(2, orderNum);
				return ps.executeUpdate();
			}
		});
	}

	/**
	 * 当orderNum在某区间的时候递增
	 * 
	 * @param parentId
	 * @param bgnOrderNum
	 * @param endOrderNum
	 * @return
	 */
	public Integer increaseOrderNumWhenBetween(final Long parentId, final Integer bgnOrderNum, final Integer endOrderNum) {
		return getJdbcTemplate().execute(SQL_INCREASE_ORDER_NUM_WHEN_BETWEEN, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setLong(1, parentId);
				ps.setInt(2, bgnOrderNum);
				ps.setInt(3, endOrderNum);
				return ps.executeUpdate();
			}
		});
	}

	/**
	 * 当orderNum在某区间的时候递减
	 * 
	 * @param parentId
	 * @param bgnOrderNum
	 * @param endOrderNum
	 * @return
	 */
	public Integer decreaseOrderNumWhenBetween(final Long parentId, final Integer bgnOrderNum, final Integer endOrderNum) {
		return getJdbcTemplate().execute(SQL_DECREASE_ORDER_NUM_WHEN_BETWEEN, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setLong(1, parentId);
				ps.setInt(2, bgnOrderNum);
				ps.setInt(3, endOrderNum);
				return ps.executeUpdate();
			}
		});
	}

	/**
	 * 当orderNum大于某值的时候递减
	 * 
	 * @param parentId
	 * @param orderNum
	 * @return
	 */
	public Integer decreaseOrderNumWhenGt(final Long parentId, final Integer orderNum) {
		return getJdbcTemplate().execute(SQL_DECREASE_ORDER_NUM_WHEN_GT, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setLong(1, parentId);
				ps.setInt(2, orderNum);
				return ps.executeUpdate();
			}
		});
	}

	/**
	 * 删除角色、资源、操作类型之间的关系
	 * 
	 * @param relaIds
	 * @param operationIds
	 * @return
	 */
	public int[] deleteRoleResourceOperationRela(final List<Long> relaIds, final List<Long> operationIds) {
		return getJdbcTemplate().batchUpdate(SQL_DELETE_ROLE_RESOURCE_OPERATION_RELA,
				new BatchPreparedStatementSetter() {

					@Override
					public void setValues(PreparedStatement ps, int i) throws SQLException {
						ps.setLong(1, relaIds.get(i));
						ps.setLong(2, operationIds.get(i));
					}

					@Override
					public int getBatchSize() {
						return relaIds.size();
					}
				});
	}

	/**
	 * 删除角色、资源、操作类型之间的关系
	 * 
	 * @param resourceIds
	 * @return
	 */
	public int[] deleteRoleResourceOperationRela(final Long[] resourceIds) {
		return getJdbcTemplate().batchUpdate(SQL_DELETE_ROLE_RESOURCE_OPERATION_RELA2,
				new BatchPreparedStatementSetter() {

					@Override
					public void setValues(PreparedStatement ps, int i) throws SQLException {
						ps.setLong(1, resourceIds[i]);
					}

					@Override
					public int getBatchSize() {
						return resourceIds.length;
					}
				});
	}

	/**
	 * 查询角色、资源、操作类型之间的关系
	 * 
	 * @param resourceId
	 * @return {rela_id,operation_id}
	 */
	public SqlRowSet queryRoleResourceOperationRela(final Long resourceId) {
		return getJdbcTemplate().queryForRowSet(SQL_QUERY_ROLE_RESOURCE_OPERATION_RELA, resourceId);
	}

	/**
	 * 删除资源、操作类型之间的关系
	 * 
	 * @param resourceId
	 * @return
	 */
	public Integer deleteResourceOperationRela(final Long resourceId) {
		return getJdbcTemplate().execute(SQL_DELETE_RESOURCE_OPERATION_RELA, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setLong(1, resourceId);
				return ps.executeUpdate();
			}
		});
	}

	/**
	 * 删除资源、操作类型之间的关系
	 * 
	 * @param resourceIds
	 * @return
	 */
	public int[] deleteResourceOperationRela(final Long[] resourceIds) {
		return getJdbcTemplate().batchUpdate(SQL_DELETE_RESOURCE_OPERATION_RELA, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setLong(1, resourceIds[i]);
			}

			@Override
			public int getBatchSize() {
				return resourceIds.length;
			}
		});
	}

	/**
	 * 新增资源、操作类型之间的关系
	 * 
	 * @param resourceId
	 * @param operationId
	 * @return
	 */
	public Integer insertResourceOperationRela(final Long resourceId, final Long operationId) {
		return getJdbcTemplate().execute(SQL_INSERT_RESOURCE_OPERATION_RELA, new PreparedStatementCallback<Integer>() {
			@Override
			public Integer doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
				ps.setLong(1, resourceId);
				ps.setLong(2, operationId);
				return ps.executeUpdate();
			}
		});
	}

	/**
	 * 新增资源、操作类型之间的关系
	 * 
	 * @param resourceId
	 * @param operationIds
	 * @return
	 */
	public int[] insertResourceOperationRela(final Long resourceId, final Long[] operationIds) {
		return getJdbcTemplate().batchUpdate(SQL_INSERT_RESOURCE_OPERATION_RELA, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setLong(1, resourceId);
				ps.setLong(2, operationIds[i]);
			}

			@Override
			public int getBatchSize() {
				return operationIds.length;
			}
		});
	}

	/**
	 * 新增资源、操作类型之间的关系
	 * 
	 * @param resourceId
	 * @param operationIds
	 * @return
	 */
	public int[] insertResourceOperationRela(final Long resourceId, final List<Long> operationIds) {
		return getJdbcTemplate().batchUpdate(SQL_INSERT_RESOURCE_OPERATION_RELA, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				ps.setLong(1, resourceId);
				ps.setLong(2, operationIds.get(i));
			}

			@Override
			public int getBatchSize() {
				return operationIds.size();
			}
		});
	}

	/**
	 * <pre>
	 * 根据用户名查询该用户有权限的操作
	 * {resource_id,operation_id,level,is_public}
	 * </pre>
	 * 
	 * @param loginName
	 * @return
	 */
	public SqlRowSet queryUserResourceOperationForRowSet(String loginName) {
		return getJdbcTemplate().queryForRowSet(SqlStatics.QUERY_USER_RESOUCE_OPERATION, loginName);
	}
}
